﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EnterpriseDataAccess.DataProviders;
using EnterpriseDataAccess.Process;
using EnterpriseDataAccess.Factory;

namespace EnterpriseDataAccess
{
    /// <summary>
    /// Clase maestra para conexiones con diferentes origenes de datos.
    /// </summary>
    public class DBMaster : IDBMaster, IDisposable
    {
        private Server server;
        private string connectionString;
        private DbDataReader idataReader;

        /// <summary>
        /// Constructor vacio de la Clase DBMaster.
        /// </summary>
        public DBMaster() { }

        /// <summary>
        /// Constructor de la Clase DBMaster.
        /// </summary>
        /// <param name="server">Establece el tipo servidor de base de datos con que se va operar.</param>
        public DBMaster(Server server)
        {
            this.server = server;
        }

        /// <summary>
        /// Constructor de la Clase DBMaster.
        /// </summary>
        /// <param name="server">Establece el tipo servidor de base de datos con que se va operar.</param>
        /// <param name="connectionString">Establece la cadena utilizada para abrir una base de datos.</param>
        public DBMaster(Server server, string connectionString)
        {
            this.server = server;
            this.connectionString = connectionString;
        }

        public Server Server
        {
            get
            {
                return server;
            }
            set
            {
                server = value;
            }
        }

        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString = value;
            }
        }

        public DbDataReader DataReader
        {
            get
            {
                return idataReader;
            }
            set
            {
                idataReader = value;
            }
        }

        public DbDataReader ExecuteReader(CommandType commandType, string commandText, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            IDBEngine db = new DBEngine(server, connectionString);

            if (parameters != null)
            {
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                this.DataReader = db.ExecuteReader(commandType, commandText);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }

            return this.DataReader;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            DataSet dt = new DataSet();
            IDBEngine db = new DBEngine(server, connectionString);

            if (parameters != null)
            {
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                dt = db.ExecuteDataSet(commandType, commandText);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }

            return dt;
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            DataTable dt = new DataTable();
            IDBEngine db = new DBEngine(server, connectionString);

            if (parameters != null)
            {
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                dt = db.ExecuteDataTable(commandType, commandText);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }

            return dt;
        }

        public object ExecuteScalar(CommandType commandType, string commandText, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            object dt = null;
            IDBEngine db = new DBEngine(server, connectionString);

            if (parameters != null)
            {
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                dt = db.ExecuteScalar(commandType, commandText);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }

            return dt;
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            int dt = 0;
            IDBEngine db = new DBEngine(server, connectionString);

            if (parameters != null)
            {
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                dt = db.ExecuteNonQuery(commandType, commandText);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }

            return dt;
        }

        public void Dispose()
        {
            if (this.DataReader != null)
                this.DataReader.Close();
            this.DataReader = null;
            GC.Collect();
            GC.SuppressFinalize(this);
        }


        public void ProcessDataTable(DataTable table, bool useTransaction = false)
        {
            IDBEngine db = new DBEngine(server, connectionString);
            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                string insertCmd = SQLCommandBuilder.InsertCommand(table);
                string updateCmd = SQLCommandBuilder.UpdateCommand(table);
                string deleteCmd = SQLCommandBuilder.DeleteCommand(table);

                foreach (DataRow row in table.Rows)
                {
                    switch (row.RowState)
                    {
                        case DataRowState.Added:
                            db.CreateParameters(row);
                            db.ExecuteNonQuery(CommandType.Text, insertCmd);
                            break;
                        case DataRowState.Deleted:
                            db.CreateParameters(row);
                            db.ExecuteNonQuery(CommandType.Text, deleteCmd);
                            break;
                        case DataRowState.Modified:
                            db.CreateParameters(row);
                            db.ExecuteNonQuery(CommandType.Text, updateCmd);
                            break;
                        default:
                            break;
                    }
                }

                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }
        }


        public void FillDataTable(DataTable table, Dictionary<string, object> parameters = null, bool useTransaction = false)
        {
            IDBEngine db = new DBEngine(server, connectionString);
            string selectCmd = "";

            if (parameters == null)
            {
                selectCmd = SQLCommandBuilder.SelectCommand(table);
            }

            else
            {
                selectCmd = SQLCommandBuilder.SelectCommand(table, true);
                db.CreateParameters(parameters);
            }

            db.Open();
            if (useTransaction) db.BeginTransaction();
            try
            {
                db.FillDataTable(ref table,CommandType.Text, selectCmd);
                if (useTransaction) db.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (useTransaction) db.RollBack();
            }
            finally
            {
                db.Dispose();
            }
        }
    }
}
